package cn.edu.swu.book;

import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.Writer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException {
        try {
            List<Book> books = this.selectBooksFromDB("SELECT * FROM book");
            response.setContentType("text/html");
            try(Writer writer = response.getWriter()) {
                writer.write("<!DOCTYPE html>" +
                        "<html lang=\"en\">" +
                        "<head>" +
                            "<meta charset=\"UTF-8\">" +
                            "<title>西南大学网上书店</title>" +
                            "<link rel=\"stylesheet\" href=\"css/bookstore.css\"/>"+
                        "</head>");
                writer.write("<body>" +
                        "<div style=\"width:99%;text-align:center;background-color:#336699;margin:0px;padding:2px\">" +
                        "<marquee><h3 style=\"color:#FFFFFF\">欢迎访问西大网上书店</h3></marquee>" +
                        "</div><center>");
                writer.write(this.buildHtmlTable(books));
                writer.write("</center></body></html>");
            }
        } catch (SQLException | IOException e) {
            throw new ServletException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    private String buildHtmlTable(List<Book> books) {
        StringBuilder sb = new StringBuilder();
        sb.append("<table width='70%' id='tb-book'>\n");
        sb.append(String.format("<tr><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>\n",
                "编号", "书名", "作者", "价格", "备注"));
        for (Book book : books) {
            sb.append("<tr>");
            sb.append(String.format("<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>",
                    book.getId(), book.getName(), book.getAuthor(), book.getPrice(), book.getMemo()));
            sb.append("</tr>\n");
        }
        sb.append("</table>\n");
        return sb.toString();
    }

    private List<Book> selectBooksFromDB(String sql) throws SQLException, ClassNotFoundException {
        List<Book> books = new ArrayList<>();
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String author = resultSet.getString("author");
                    double price = resultSet.getDouble("price");
                    String memo = resultSet.getString("memo");
                    Book book = new Book(id, name, author, price, memo);
                    books.add(book);
                }
            }
        }
        return books;
    }

    private Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String dbUser = "root";
        String dbPass = "123456";
        String dbUrl  = "jdbc:mysql://127.0.0.1:3306/bookstore?useUnicode=true&characterEncoding=utf8";
        Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass);
        return connection;
    }


}
